| MySQL中查询一个字符串字段的值不为空到底该怎么写？ | 您所在的位置：网站首页 › sql 数据不为空 › MySQL中查询一个字符串字段的值不为空到底该怎么写？ |
|
如果你看过SQL开发规范，肯定知道尽量 不要对字段使用函数 但是，就好像三大范式中我只遵循第一范式而忽略后面两个范式一样，千万不要教条！ 如果你要查询表中指定的字符串类型的字段的值不为空时，通常都是 column_name != '' ，也可能有人会写 LENGTH(column_name) > 0 ,或者 CHAR_LENGTH(column_name) > 0 先看一下构造数据的SQL: DROP TABLE IF EXISTS `not_empty_query`; CREATE TABLE `not_empty_query` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `related_id` varchar(50) NOT NULL DEFAULT '', PRIMARY KEY(`id`) ) ENGINE=InnoDB COMMENT '查询字段值不为空'; -- 创建填充数据的存储过程 DROP PROCEDURE IF EXISTS `mysp_fill_table_not_empty_query`; DELIMITER $$ CREATE PROCEDURE `mysp_fill_table_not_empty_query`(in n int) BEGIN DECLARE i INT UNSIGNED; SET i = 0; START TRANSACTION; WHILE i < n DO INSERT INTO `not_empty_query`(`related_id`)VALUES(uuid()); SET i = i + 1; END WHILE; COMMIT; END $$ DELIMITER ; -- 调用存储过程, 插入100w行数据 CALL `mysp_fill_table_not_empty_query`(1000000); DROP PROCEDURE IF EXISTS `mysp_fill_table_not_empty_query`;上面的代码向表 not_empty_query 中插入了100万行数据。（代码中产生的每行的长度都是一样，这里只是为了方便） 然后来看下3种查询字段值不为空的SQL的explain结果: mysql> EXPLAIN SELECT SQL_NO_CACHE COUNT(*) FROM `not_empty_query` WHERE `related_id` != ''; +----+-------------+-----------------+------------+------+---------------+------+---------+------+--------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------------+------------+------+---------------+------+---------+------+--------+----------+-------------+ | 1 | SIMPLE | not_empty_query | NULL | ALL | NULL | NULL | NULL | NULL | 996442 | 90.00 | Using where | +----+-------------+-----------------+------------+------+---------------+------+---------+------+--------+----------+-------------+ 1 row in set, 2 warnings (0.00 sec) mysql> EXPLAIN SELECT SQL_NO_CACHE COUNT(*) FROM `not_empty_query` WHERE LENGTH(`related_id`) > 0; +----+-------------+-----------------+------------+------+---------------+------+---------+------+--------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------------+------------+------+---------------+------+---------+------+--------+----------+-------------+ | 1 | SIMPLE | not_empty_query | NULL | ALL | NULL | NULL | NULL | NULL | 996442 | 100.00 | Using where | +----+-------------+-----------------+------------+------+---------------+------+---------+------+--------+----------+-------------+ 1 row in set, 2 warnings (0.00 sec) mysql> EXPLAIN SELECT SQL_NO_CACHE COUNT(*) FROM `not_empty_query` WHERE CHAR_LENGTH(`related_id`) > 0; +----+-------------+-----------------+------------+------+---------------+------+---------+------+--------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------------+------------+------+---------------+------+---------+------+--------+----------+-------------+ | 1 | SIMPLE | not_empty_query | NULL | ALL | NULL | NULL | NULL | NULL | 996442 | 100.00 | Using where | +----+-------------+-----------------+------------+------+---------------+------+---------+------+--------+----------+-------------+ 1 row in set, 2 warnings (0.00 sec)唯一的差别在于 filtered 字段， 只有第一个 related_id != '' 是 90.00。这个其实是没有多大意义的。（更多信息可参考 官方文档 及 MySQL explain中的filtered的意义 ） 根据MySQL存储数据的格式我们知道，每一行数据的所有字段的内容是连续存储在一起的的（text等类型除外），对于int、char(n)这种定长字段，我们从字段定义中就知道它们的长度，对于varchar(n)这种只有最大长度的字符串字段，必须存储其字节长度值。 对于这三种判断字段是否为空的方法，其处理过程应该是： column_name != '' 取出字段的值, 然后和空字符串进行比较 LENGTH(column_name) > 0 从行数据的元数据中直接取出字节长度值, 然后与0比较 CHAR_LENGTH(column_name) > 0 取出字段的值, 然后根据CHARSET计算字符串长度, 然后与0比较根据上面的分析, 理论认为其查询速度是: 2 > 1 > 3 我们在本地多次执行的结果, 与理论分析一致： 方式 4次执行时间(s) 1. column_name != '' 0.19, 0.19, 0.19, 0.19 2. LENGTH(column_name) > 0 0.17, 0.17, 0.16, 0.16 3. CHAR_LENGTH(column_name) > 0 0.25, 0.25, 0.25, 0.25当然， 如果表的字段比较多，或者总长度比较大，或者包含了text等类型的字段，则情况又更为复杂，这里暂不讨论。 |
| CopyRight 2018-2019 实验室设备网 版权所有 |